From 01d73963b14a2cd4fc854d80c34ecad7e1e872eb Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 15 Jul 2026 22:31:21 +0000 Subject: [PATCH] Create, document, index, and trigger FERTILITY --- db/schemas/lib/triggers/Makefile | 1 + .../lib/triggers/create/biography_data.m4 | 58 ++++++- db/schemas/lib/triggers/create/fertility.m4 | 163 ++++++++++++++++++ db/schemas/lib/triggers/drop/fertility.m4 | 24 +++ db/schemas/sokwedb/indexes/Makefile | 2 +- .../sokwedb/indexes/create/fertility.m4 | 35 ++++ db/schemas/sokwedb/indexes/drop/fertility.m4 | 29 ++++ db/schemas/sokwedb/tables/Makefile | 3 +- db/schemas/sokwedb/tables/create/fertility.m4 | 39 +++++ doc/src/analyzed.m4 | 1 + doc/src/analyzed/fertility.m4 | 135 +++++++++++++++ doc/src/epilog.inc.m4 | 16 ++ 12 files changed, 503 insertions(+), 3 deletions(-) create mode 100644 db/schemas/lib/triggers/create/fertility.m4 create mode 100644 db/schemas/lib/triggers/drop/fertility.m4 create mode 100644 db/schemas/sokwedb/indexes/create/fertility.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/fertility.m4 create mode 100644 db/schemas/sokwedb/tables/create/fertility.m4 create mode 100644 doc/src/analyzed/fertility.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index c08b919..4714c9e 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -35,6 +35,7 @@ ORDER := comm_ids \ arrivals \ aggressions \ sightings \ + fertility \ food_events \ groomings \ groom_scans_b \ diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 51dc747..ba9f366 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -1266,7 +1266,63 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END IF; END; - END IF; + + + -- FERTILITY + -- The individual must be female to have a record of fertility + IF NEW.sex <>'sdb_female' AND + OLD.sex = 'sdb_female' THEN + DECLARE + a_id fertility.id%TYPE; + a_study fertility.study%TYPE; + a_animid fertility.animid%TYPE; + a_startdate fertility.startdate%TYPE; + a_starttype fertility.starttype%TYPE; + a_stopdate fertility.stopdate%TYPE; + a_stoptype fertility.stoptype%TYPE; + + BEGIN + SELECT fertility.id, fertility.study, fertility.animid + , fertility.startdate, fertility.starttype + , fertility.stopdate, fertility.stoptype + INTO a_id , a_study , a_animid + , a_startdate , a_starttype + , a_stopdate , a_stoptype + FROM fertility + WHERE fertility.animid = NEW.animid + -- Produce a consistent error message + ORDER BY fertility.startdate, fertility.study; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot have records of fertility' + || ' state unless they are female' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (Sex) = (' + || NEW.sex + || '), Value (BirthDate) = (' + || NEW.birthdate + || ': Key (FERTILITY.ID) = (' + || a_id + || '), Value (FERTILITY.Study) = (' + || a_study + || '), Value (FERTILITY.AnimID) = (' + || a_animid + || '), Value (FERTILITY.StartDate) = (' + || a_startdate + || '), Value (FERTILITY.StartType) = (' + || a_starttype + || '), Value (FERTILITY.StopDate) = (' + || a_stopdate + || '), Value (FERTILITY.StopType) = (' + || a_stoptype + || ')'; + END IF; + END; + END IF; + END IF; -- TG_OP = 'UPDATE' RETURN NULL; END; diff --git a/db/schemas/lib/triggers/create/fertility.m4 b/db/schemas/lib/triggers/create/fertility.m4 new file mode 100644 index 0000000..a21f838 --- /dev/null +++ b/db/schemas/lib/triggers/create/fertility.m4 @@ -0,0 +1,163 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify it +dnl under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Triggers for the fertility table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +RAISE INFO 'fertility_func'; +CREATE OR REPLACE FUNCTION fertility_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for fertility insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`FERTILITY', `ID') + END IF; + + IF TG_OP = 'INSERT' + OR NEW.animid <> OLD.animid THEN + -- The individual must be female. + DECLARE + -- BIOGRAPHY_DATA + a_sex biography_data.sex%TYPE; + a_birthdate biography_data.birthdate%TYPE; + + BEGIN + -- Get the data we need, and that needed for error message content too + SELECT biography_data.sex, biography_data.birthdate + INTO a_sex , a_birthdate + FROM biography_data + WHERE biography_data.animid = NEW.animid + AND biography_data.sex <> 'sdb_female'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of FERTILITY' + , DETAIL = 'The AnimID must be that of a female' + || ': Key (ID) = (' + || NEW.id + || '): Value (Study) = (' + || NEW.study + || '): Value (AnimID) = (' + || NEW.animid + || '): Value (StartDate) = (' + || NEW.startdate + || '): Value (StartType) = (' + || NEW.starttype + || '): Value (StopDate) = (' + || NEW.stopdate + || '): Value (StopType) = (' + || NEW.stoptype + || '): Key (BIOGRAPHY_DATA.AnimID) = (' + || NEW.animid + || '), Value (BIOGRAPHY_DATA.Sex) = (' + || a_sex + || '): Value (BIOGRAPHY_DATA.BirthDate) = (' + || a_birthdate + || ')'; + END IF; + END; + END IF; + + IF TG_OP = 'INSERT' + OR (NEW.startdate <> OLD.startdate + OR NEW.stopdate <> OLD.stopdate) THEN + -- Periods of fertility cannot overlap. + DECLARE + a_id fertility.id%TYPE; + a_study fertility.study%TYPE; + a_animid fertility.animid%TYPE; + a_startdate fertility.startdate%TYPE; + a_starttype fertility.starttype%TYPE; + a_stopdate fertility.stopdate%TYPE; + a_stoptype fertility.stoptype%TYPE; + + BEGIN + SELECT fertility.id, fertility.study, fertility.animid + , fertility.startdate, fertility.starttype + , fertility.stopdate, fertility.stoptype + INTO a_id , a_study , a_animid + , a_startdate , a_starttype + , a_stopdate , a_stoptype + FROM fertility + WHERE fertility.id <> NEW.id + AND fertility.study = NEW.study + AND fertility.animid = NEW.animid + AND ((fertility.startdate <= NEW.startdate + AND fertility.stopdate >= NEW.startdate) + OR (fertility.startdate <= NEW.stopdate + AND fertility.stopdate >= NEW.stopdate)) + -- Produce a consistent error message + ORDER BY fertility.id; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of FERTILITY' + , DETAIL = 'Per Study, per AnimID, the StartDate and StopDate' + || ' (inclusive) must not overlap, but a row does' + || ': Key (ID) = (' + || NEW.id + || '): Value (Study) = (' + || NEW.study + || '): Value (AnimID) = (' + || NEW.animid + || '): Value (StartDate) = (' + || NEW.startdate + || '): Value (StartType) = (' + || NEW.starttype + || '): Value (StopDate) = (' + || NEW.stopdate + || '): Value (StopType) = (' + || NEW.stoptype + || ': Overlapping row has Key (FERTILITY.ID) = (' + || a_id + || '): Value (FERTILITY.Study) = (' + || a_study + || '): Value (FERTILITY.AnimID) = (' + || a_animid + || '): Value (FERTILITY.StartDate) = (' + || a_startdate + || '): Value (FERTILITY.StartType) = (' + || a_starttype + || '): Value (FERTILITY.StopDate) = (' + || a_stopdate + || '): Value (FERTILITY.StopType) = (' + || a_stoptype + || ')'; + END IF; + END; + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'fertility_trigger'; +CREATE TRIGGER fertility_trigger + AFTER INSERT OR UPDATE + ON fertility FOR EACH ROW + EXECUTE PROCEDURE fertility_func(); diff --git a/db/schemas/lib/triggers/drop/fertility.m4 b/db/schemas/lib/triggers/drop/fertility.m4 new file mode 100644 index 0000000..97884cc --- /dev/null +++ b/db/schemas/lib/triggers/drop/fertility.m4 @@ -0,0 +1,24 @@ +dnl Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop triggers for fertility table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS fertility_func() CASCADE; + diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index 9bcc876..2e09322 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -24,7 +24,7 @@ ORDER := biography_data biography_log comm_membs comm_memb_log \ swelling_sources swelling_states aggression_event_log sightings \ aggressions food_events groomings attendance \ arrivals_a species_present repro_states locations_utm \ - locations_paper colobus matings + locations_paper colobus matings fertility ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/fertility.m4 b/db/schemas/sokwedb/indexes/create/fertility.m4 new file mode 100644 index 0000000..1aa174c --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/fertility.m4 @@ -0,0 +1,35 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +CREATE INDEX IF NOT EXISTS fertility_study ON fertility + (study); +CREATE INDEX IF NOT EXISTS fertility_animid ON fertility + (animid); +CREATE INDEX IF NOT EXISTS fertility_startdate ON fertility + (startdate); +CREATE INDEX IF NOT EXISTS fertility_starttype ON fertility + (starttype); +CREATE INDEX IF NOT EXISTS fertility_stopdate ON fertility + (stopdate); +CREATE INDEX IF NOT EXISTS fertility_stoptype ON fertility + (stoptype); diff --git a/db/schemas/sokwedb/indexes/drop/fertility.m4 b/db/schemas/sokwedb/indexes/drop/fertility.m4 new file mode 100644 index 0000000..0c03602 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/fertility.m4 @@ -0,0 +1,29 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +DROP INDEX IF EXISTS fertility_study; +DROP INDEX IF EXISTS fertility_animid; +DROP INDEX IF EXISTS fertility_startdate; +DROP INDEX IF EXISTS fertility_starttype; +DROP INDEX IF EXISTS fertility_stopdate; +DROP INDEX IF EXISTS fertility_stoptype; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 0c6ec67..98b498e 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -49,7 +49,8 @@ ORDER := biography_data \ pantgrunts \ brecord_notes \ colobus \ - matings + matings \ + fertility ## ## CAUTION: This Makefile is not designed to be run directly. It is normally ## invoked by another Makefile. diff --git a/db/schemas/sokwedb/tables/create/fertility.m4 b/db/schemas/sokwedb/tables/create/fertility.m4 new file mode 100644 index 0000000..ee30b38 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/fertility.m4 @@ -0,0 +1,39 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl +dnl + +CREATE TABLE fertility ( + key_column(`FERTILITY', `ID', `INTEGER') + ,study TEXT NOT NULL + REFERENCES studies + ,animid_column + ,startdate DATE NOT NULL + ,starttype TEXT NOT NULL + REFERENCES fertility_starts + ,stopdate DATE NOT NULL + ,stoptype TEXT NOT NULL + REFERENCES fertility_stops +); + +grant_priv(`FERTILITY') diff --git a/doc/src/analyzed.m4 b/doc/src/analyzed.m4 index f444ffd..ad961e7 100644 --- a/doc/src/analyzed.m4 +++ b/doc/src/analyzed.m4 @@ -31,6 +31,7 @@ a rudimentary analytical process. .. toctree:: :maxdepth: 3 + analyzed/fertility.rst analyzed/repro_states.rst analyzed/swelling_states.rst analyzed/sightings.rst diff --git a/doc/src/analyzed/fertility.m4 b/doc/src/analyzed/fertility.m4 new file mode 100644 index 0000000..a67fd9b --- /dev/null +++ b/doc/src/analyzed/fertility.m4 @@ -0,0 +1,135 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + +.. _FERTILITY: + +FERTILITY +--------- + +.. |FERTILITY_summary| replace:: + + Each row represents a period of time during which a female is + fertile. + +|FERTILITY_summary| + +The |FERTILITY.AnimID| must be that of a female. +This means that the related |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| +value must be ``sdb_female``. + +For any given female, for any given study, the periods of fertility +cannot overlap. +This means that, per female, per study, the |FERTILITY.StopDate| of +any given row cannot be between, inclusive, the |FERTILITY.StartDate| +and the |FERTILITY.StopDate| of any other row. + + +.. contents:: + :depth: 2 + + +.. _FERTILITY.ID: + +ID (IDentifier) +``````````````` + +.. |FERTILITY.ID_summary| replace:: + |idcol| + +|FERTILITY.ID_summary| +|notnull| + + +.. _FERTILITY.Study: + +Study +````` + +.. |FERTILITY.Study_summary| replace:: + A code designating the the study that produced the fertility data. + A |STUDIES|.\ |STUDIES.Study| value. + +|FERTILITY.Study_summary| +|notnull| + + +.. _FERTILITY.AnimID: + +AnimID +`````` + +.. |FERTILITY.AnimID_summary| replace:: + The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the female. + +|FERTILITY.AnimID_summary| +|notnull| + + +.. _FERTILITY.StartDate: + +StartDate +````````` + +.. |FERTILITY.StartDate_summary| replace:: + The date, inclusive, of the start of the period of fertility. + +|FERTILITY.StartDate_summary| +|notnull| + + +.. _FERTILITY.StartType: + +StartType +````````` + +.. |FERTILITY.StartType_summary| replace:: + A code indicating the female's fertility state at the start of the + time period. + A |FERTILITY_STARTS|.\ |FERTILITY_STARTS.Code| value. + +|FERTILITY.StartType_summary| +|notnull| + + +.. _FERTILITY.StopDate: + +StopDate +```````` + +.. |FERTILITY.StopDate_summary| replace:: + The date, inclusive, of the end of the period of fertility. + +|FERTILITY.StopDate_summary| +|notnull| + + +.. _FERTILITY.StopType: + +StopType +```````` + +.. |FERTILITY.StopType_summary| replace:: + A code indicating the female's fertility state at the end of the + time period. + A |FERTILITY_STOPS|.\ |FERTILITY_STOPS.Code| value. + +|FERTILITY.StopType_summary| +|notnull| diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 31b2168..c1c0b25 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -407,6 +407,22 @@ sdb_generated_rst()dnl .. |EVENTS.Notes| replace:: :ref:`Notes ` +.. |FERTILITY| replace:: :ref:`EVENTS ` +.. |FERTILITY.ID| replace:: + :ref:`ID ` +.. |FERTILITY.Study| replace:: + :ref:`Study ` +.. |FERTILITY.AnimID| replace:: + :ref:`AnimID ` +.. |FERTILITY.StartDate| replace:: + :ref:`StartDate ` +.. |FERTILITY.StartType| replace:: + :ref:`StartType ` +.. |FERTILITY.StopDate| replace:: + :ref:`Stopdate ` +.. |FERTILITY.StopType| replace:: + :ref:`StopType ` + .. |FERTILITY_STARTS| replace:: :ref:`FERTILITY_STARTS ` .. |FERTILITY_STARTS.Code| replace:: :ref:`Code ` -- 2.34.1